(1) Field of the Invention
The present invention relates to a method for generating queries, in particular for use with relational databases, and also to a query generation system for generating such queries.
(2) Description of Related Art
Query generators, for example Discoverer® from Oracle®, are used to construct database queries which are then sent to a database for execution. A user constructs a query by selecting items from a drop-down list of items displayed on the screen. The items may represent data which are to be obtained from the database, or alternatively they may represent operations that are to be performed on these data. Once the items have been selected, the query generator then generates a query, usually in structured query language (SQL) for execution by the database.
In many cases, the SQL generated for a given combination of items selected by a user is not ambiguous, and the user sees the results they expect based on the items and operations selected.
However, some types of query operate over multiple levels of aggregation. For example, the following crosstabular report output shows two levels of aggregation:
RegionCitySum(Profit)East100New York50Boston20Atlanta30
The lowest level of aggregation in this case is (Region, City), that is the Sum(Profit) values are aggregated for each of the cities (New York, Boston and Atlanta) within the East Region. This lowest level of aggregation is known as the base level of aggregation. The highest level (and in this case, the only other level) of aggregation is (Region) in which the Sum(Profit) value is the total of all those of lower level.
There are situations in which the normal SQL generation algorithms of query tools cause SQL statements to be generated that yield results which, although relationally correct, are not what the user actually required. Typically, this occurs when the selected items require the calculation of aggregates of data in a plurality of separate detail tables joined to a single master table. Specific examples of such situations will be described in more detail below.
Some query tools simply present the misleading data to users without any warning or require an administrator to set up complex structures in order to generate the correct results. Rather than present data to the user which is likely to be misinterpreted, Oracle® Discoverer® generates an SQL query which will compute the base aggregation level only leaving higher levels of aggregation to be computed locally by Discoverer®. There are situations in which Discoverer® cannot compute the higher levels of aggregation correctly, leaving two options: not displaying any values for higher levels of aggregation or displaying incorrect values for higher levels of aggregation.
Clearly, there is a need for a query generator that can generate queries that reliably return data for which all aggregation has been correctly computed in all circumstances.